package com.component;

import com.Listener.ActionDoneListener;
import com.dao.DeleteUser;
import com.util.BaseDao;

import javax.swing.*;
import javax.swing.table.JTableHeader;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.*;

public class StatisticManageComponent extends Box {
    final int WIDTH=1000;
    final int HEIGHT=600;

    private int FarmerID;
    JFrame jf = null;
    private JTable table;
    private JTableHeader jth;
    private JScrollPane scrollPane;
    private Object[][] info;

    public StatisticManageComponent(JFrame jf,int FarmerID) {
        //垂直布局
        super(BoxLayout.Y_AXIS);
        //组装视图
        this.jf = jf;
        this.FarmerID = FarmerID;

        scrollPane = new JScrollPane();
        JPanel btnPanel = new JPanel();
        Color color = new Color(203, 220, 217);
        btnPanel.setBackground(color); //设置背景颜色
        btnPanel.setMaximumSize(new Dimension(WIDTH, 80)); //设置最大宽高
        btnPanel.setLayout(new FlowLayout(FlowLayout.RIGHT)); // 默认的panel是从左侧开始的流式布局，此处设置为从右侧开始布局的流式布局

        JButton weekBtn = new JButton("按周统计销售信息");
        JButton monthBtn = new JButton("按月统计销售信息");
        JButton quarterBtn = new JButton("按季度统计销售信息");
        JButton yearBtn = new JButton("按年统计销售信息");

        //week的监听
        weekBtn.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                //弹出一个对话框，让用户输入统计信息的时间段
                String startDate = JOptionPane.showInputDialog(jf,"请输入开始统计的日期：","按周统计农产品销售信息",JOptionPane.PLAIN_MESSAGE);
                if(startDate!=null){
                    String endDate = JOptionPane.showInputDialog(jf,"请输入结束统计的日期：","按周统计农产品销售信息",JOptionPane.PLAIN_MESSAGE);
                    if(endDate!=null){
                        weeklyRequestProductStatisticData(FarmerID,startDate,endDate);
                    }
                }

            }
        });

        //month的监听
        monthBtn.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                //弹出一个对话框，让用户输入统计信息的时间段
                String startDate = JOptionPane.showInputDialog(jf,"请输入开始统计的日期：","按月统计农产品销售信息",JOptionPane.PLAIN_MESSAGE);
                if(startDate!=null) {
                    String endDate = JOptionPane.showInputDialog(jf, "请输入结束统计的日期：", "按月统计农产品销售信息", JOptionPane.PLAIN_MESSAGE);
                    if(endDate!=null) {
                        monthlyRequestProductStatisticData(FarmerID, startDate, endDate);
                    }
                }
            }
        });

        //quarter的监视
        quarterBtn.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                //弹出一个对话框，让用户输入统计信息的时间段
                String startDate = JOptionPane.showInputDialog(jf,"请输入开始统计的日期：","按季度统计农产品销售信息",JOptionPane.PLAIN_MESSAGE);
                if(startDate!=null) {
                    String endDate = JOptionPane.showInputDialog(jf, "请输入结束统计的日期：", "按季度统计农产品销售信息", JOptionPane.PLAIN_MESSAGE);
                    if(endDate!=null) {
                        quarterlyRequestProductStatisticData(FarmerID, startDate, endDate);
                    }
                }
            }
        });

        //year的监视
        yearBtn.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                //弹出一个对话框，让用户输入统计信息的时间段
                String startDate = JOptionPane.showInputDialog(jf,"请输入开始统计的日期：","按年统计农产品销售信息",JOptionPane.PLAIN_MESSAGE);
                if(startDate!=null) {
                    String endDate = JOptionPane.showInputDialog(jf, "请输入结束统计的日期：", "按年统计农产品销售信息", JOptionPane.PLAIN_MESSAGE);
                    if(endDate!=null) {
                        yearlyRequestProductStatisticData(FarmerID, startDate, endDate);
                    }
                }
            }
        });

        //组装表格
        btnPanel.add(weekBtn);
        btnPanel.add(monthBtn);
        btnPanel.add(quarterBtn);
        btnPanel.add(yearBtn);
        this.add(btnPanel); // 将按钮添加进去


        //向数据库请求数据
        weeklyRequestProductStatisticData(FarmerID,"2023-01-01","2023-12-31"); //默认显示2023年来按周统计的数据

        //设置只能选中一行
        table.setSelectionMode(ListSelectionModel.SINGLE_SELECTION);

    }

    //方法
    //按周统计数据
    public void weeklyRequestProductStatisticData(int FarmerID,String StartDate,String EndDate) {
        Connection conn = new BaseDao().getConn();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            String sql = "exec dbo.WeeklySalesReport @FarmerID = ?, @StartDate = ?, @EndDate = ?;";
            ps = conn.prepareStatement(sql); //编译sql语句
            ps.setInt(1,FarmerID);
            ps.setString(2,StartDate);
            ps.setString(3,EndDate);

            rs = ps.executeQuery();
            int count = 0;
            while(rs.next()){
                count++;
            }
            rs = ps.executeQuery();
            // 将查询获得的记录数据，转换成适合生成JTable的数据形式
            String[] ts = {"第x周","产品名称","产品数量"};
            info = new Object[count][3];
            count = 0;
            while(rs.next()){
                info[count][0] = "第"+rs.getInt("WeekNumber")+"周";
                info[count][1] = rs.getString("ProductName");
                info[count][2] = rs.getString("TotalSales");
                count++;
            }
            this.table = new JTable(info,ts){
                @Override //设置不可编辑
                public boolean isCellEditable(int row, int column) {
                    return false;
                }
            };
            //显示表头
            this.jth = this.table.getTableHeader();
            //设置滚动条
            this.scrollPane.getViewport().add(table);
            this.add(scrollPane);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            System.out.println("连接数据库失败！");
        }finally{
            //关闭coon
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

            //关闭ps
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

            //关闭rs
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //按月统计数据
    public void monthlyRequestProductStatisticData(int FarmerID,String StartDate,String EndDate) {
        Connection conn = new BaseDao().getConn();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            String sql = "exec dbo.MonthlySalesReport @FarmerID = ?, @StartDate = ?, @EndDate = ?;";
            ps = conn.prepareStatement(sql); //编译sql语句
            ps.setInt(1,FarmerID);
            ps.setString(2,StartDate);
            ps.setString(3,EndDate);

            rs = ps.executeQuery();
            int count = 0;
            while(rs.next()){
                count++;
            }
            rs = ps.executeQuery();
            // 将查询获得的记录数据，转换成适合生成JTable的数据形式
            String[] ts = {"第x月","产品名称","产品数量"};
            info = new Object[count][3];
            count = 0;
            while(rs.next()){
                info[count][0] = "第"+rs.getInt("MonthNumber")+"月";
                info[count][1] = rs.getString("ProductName");
                info[count][2] = rs.getString("TotalSales");
                count++;
            }
            this.table = new JTable(info,ts){
                @Override //设置不可编辑
                public boolean isCellEditable(int row, int column) {
                    return false;
                }
            };
            //显示表头
            this.jth = this.table.getTableHeader();
            //设置滚动条
            this.scrollPane.getViewport().add(table);
            this.add(scrollPane);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            System.out.println("连接数据库失败！");
        }finally{
            //关闭coon
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

            //关闭ps
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

            //关闭rs
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //按季度统计数据
    public void quarterlyRequestProductStatisticData(int FarmerID,String StartDate,String EndDate) {
        Connection conn = new BaseDao().getConn();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            String sql = "exec dbo.QuarterlySalesReport @FarmerID = ?, @StartDate = ?, @EndDate = ?;";
            ps = conn.prepareStatement(sql); //编译sql语句
            ps.setInt(1,FarmerID);
            ps.setString(2,StartDate);
            ps.setString(3,EndDate);

            rs = ps.executeQuery();
            int count = 0;
            while(rs.next()){
                count++;
            }
            rs = ps.executeQuery();
            // 将查询获得的记录数据，转换成适合生成JTable的数据形式
            String[] ts = {"第x季度","产品名称","产品数量"};
            info = new Object[count][3];
            count = 0;
            while(rs.next()){
                info[count][0] = "第"+rs.getInt("QuarterNumber")+"季度";
                info[count][1] = rs.getString("ProductName");
                info[count][2] = rs.getString("TotalSales");
                count++;
            }
            this.table = new JTable(info,ts){
                @Override //设置不可编辑
                public boolean isCellEditable(int row, int column) {
                    return false;
                }
            };
            //显示表头
            this.jth = this.table.getTableHeader();
            //设置滚动条
            this.scrollPane.getViewport().add(table);
            this.add(scrollPane);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            System.out.println("连接数据库失败！");
        }finally{
            //关闭coon
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

            //关闭ps
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

            //关闭rs
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //按年统计数据
    public void yearlyRequestProductStatisticData(int FarmerID,String StartDate,String EndDate) {
        Connection conn = new BaseDao().getConn();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            String sql = "exec dbo.YearlySalesReport @FarmerID = ?, @StartDate = ?, @EndDate = ?;";
            ps = conn.prepareStatement(sql); //编译sql语句
            ps.setInt(1,FarmerID);
            ps.setString(2,StartDate);
            ps.setString(3,EndDate);

            rs = ps.executeQuery();
            int count = 0;
            while(rs.next()){
                count++;
            }
            rs = ps.executeQuery();
            // 将查询获得的记录数据，转换成适合生成JTable的数据形式
            String[] ts = {"第x年","产品名称","产品数量"};
            info = new Object[count][3];
            count = 0;
            while(rs.next()){
                info[count][0] = rs.getInt("YearNumber")+"年";
                info[count][1] = rs.getString("ProductName");
                info[count][2] = rs.getString("TotalSales");
                count++;
            }
            this.table = new JTable(info,ts){
                @Override //设置不可编辑
                public boolean isCellEditable(int row, int column) {
                    return false;
                }
            };
            //显示表头
            this.jth = this.table.getTableHeader();
            //设置滚动条
            this.scrollPane.getViewport().add(table);
            this.add(scrollPane);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            System.out.println("连接数据库失败！");
        }finally{
            //关闭coon
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

            //关闭ps
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

            //关闭rs
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}

